/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package east2west.dao;

import east2west.dto.CarOrderDTO;
import east2west.dto.CustomerDTO;
import east2west.interfaces.CarOrderInterface;
import east2west.util.DatabaseUtil;
import east2west.util.DateTimeUtil;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author anhtuan
 */
public class CarOrderDAO implements CarOrderInterface {

    Connection con = null;
    CarOrderDTO carorder = null;
    CallableStatement call = null;
    Statement st = null;
    PreparedStatement pst = null;
    ResultSet rs = null;
    private final String SQL_READ_BY_ALL = "select * from V_CarOrder";
    private final String SQL_READ_BY_ID = "select * from V_CarOrders where OrderId = ?";
    private final String SQL_READ_BY_CUSTID = "{call sp_Call_View_CarOrders_FindCusId (?)}";
    private final String SQL_READ_BY_DATE = "{call sp_Call_View_CarOrders_FindDate (?)}";
    private final String SQL_CREATE = "{call sp_Insert_CarOrders (?,?,?,?)}";
    private final String SQL_UPDATE = "{call sp_Update_CarOrders (?,?,?,?,?)}";
    private final String SQL_DELETE = "{call sp_Delete_CarOrders (?)}";
//    private final String SQL_DELETE_CARID = "{call sp_Delete_CarDetail_CarId (?)}";

    public ArrayList<CarOrderDTO> readByAll() {
        ArrayList<CarOrderDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_ALL);
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDTO>();
            while (rs.next()) {
                carorder = new CarOrderDTO();
                carorder.setOrderId(rs.getInt(1));
                CustomerDAO cusDAO = new CustomerDAO();
                CustomerDTO cusDTO = cusDAO.findById(rs.getInt(2));
                carorder.setCusDTO(cusDTO);

                Date date = rs.getDate(3);
                if (date != null) {
                    carorder.setOrderDate(DateTimeUtil.convert2String(date, "MM/dd/yyyy"));
                } else {
                    carorder.setOrderDate("");
                }
                carorder.setAmount(rs.getFloat(4));
                carorder.setIsDeleted(rs.getByte(5));
                list.add(carorder);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public CarOrderDTO findById(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_ID);
            call.setInt(1, id);
            rs = call.executeQuery();
            while (rs.next()) {
                carorder = new CarOrderDTO();
                carorder.setOrderId(rs.getInt(1));
                carorder.setCustId(rs.getInt(2));
                Date date = rs.getDate(3);
                if (date != null) {
                    carorder.setOrderDate(DateTimeUtil.convert2String(date, "MM/dd/yyyy"));
                } else {
                    carorder.setOrderDate("");
                }
                carorder.setAmount(rs.getFloat(4));
                carorder.setIsDeleted(rs.getByte(5));
            }
            rs.close();
            call.close();
            return carorder;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDTO> findByCusId(int id) {
        ArrayList<CarOrderDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_CUSTID);
            call.setInt(1, id);
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDTO>();
            while (rs.next()) {
                carorder = new CarOrderDTO();
                carorder.setOrderId(rs.getInt(1));
                carorder.setCustId(rs.getInt(2));
                if (rs.getDate(3) != null) {
                    carorder.setOrderDate(DateTimeUtil.convert2String(rs.getDate(3), "MM/dd/yyyy"));
                } else {
                    carorder.setOrderDate("");
                }
                carorder.setAmount(rs.getFloat(4));
                carorder.setIsDeleted(rs.getByte(5));
                list.add(carorder);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDTO> findByDate(String date) {
        ArrayList<CarOrderDTO> list = null;
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_READ_BY_DATE);
            call.setDate(1, DateTimeUtil.convert2Date(date, "dd-MM-yyyy"));
            rs = call.executeQuery();
            list = new ArrayList<CarOrderDTO>();
            while (rs.next()) {
                carorder = new CarOrderDTO();
                carorder.setOrderId(rs.getInt(1));
                carorder.setCustId(rs.getInt(2));
                if (rs.getDate(3) != null) {
                    carorder.setOrderDate(DateTimeUtil.convert2String(rs.getDate(3), "dd-MM-yyyy"));
                } else {
                    carorder.setOrderDate("");
                }
                carorder.setAmount(rs.getFloat(4));
                carorder.setIsDeleted(rs.getByte(5));
                list.add(carorder);
            }
            rs.close();
            call.close();
            return list;
        } catch (Exception ex) {
            return null;
        } finally {
            DatabaseUtil.closeConnection(con);
        }
    }

    public ArrayList<CarOrderDTO> findByDTO(CarOrderDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public int create(CarOrderDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall(SQL_CREATE);
            call.setInt(1, dto.getCustId());
            call.setString(2, dto.getOrderDate());
            call.setFloat(3, dto.getAmount());
            call.registerOutParameter(4, java.sql.Types.INTEGER);
            call.executeUpdate();
            int orderId = call.getInt(4);
            return orderId;
        } catch (Exception ex) {
            return 0;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean update(CarOrderDTO dto) {
        try {
            con = DatabaseUtil.getConnection();
            con.setAutoCommit(false);
            call = con.prepareCall(SQL_UPDATE);
            call.setInt(1, dto.getCustId());
            call.setDate(2, DateTimeUtil.convert2Date(dto.getOrderDate(), "dd-MM-yyyy"));
            call.setFloat(3, dto.getAmount());
            call.setByte(4, dto.getIsDeleted());
            call.executeUpdate();
            con.commit();
            con.setAutoCommit(true);
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            try {
                DatabaseUtil.closeConnection(con);
                call.close();
            } catch (SQLException ex) {
                Logger.getLogger(CarOrderDAO.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public boolean delete(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("DELETE FROM CarOrders  WHERE OrderId = ?");
            call.setInt(1, id);
            call.executeUpdate();
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            DatabaseUtil.closeConnection(con);

        }
    }

    public boolean delete(CarOrderDTO dto) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean update(int id) {
        try {
            con = DatabaseUtil.getConnection();
            call = con.prepareCall("UPDATE CarOrders   SET  IsDeleted = 1 WHERE OrderId = ?");
            call.setInt(1, id);
            call.executeUpdate();
            return true;
        } catch (Exception ex) {
            return false;
        } finally {
            DatabaseUtil.closeConnection(con);

        }
    }
}
